__author__ = 'Administrator'

import pandas as pd
import sys
sys.path.append("d:\\repo\\CommonPyLib\\")
import DB_MySQL
import time
import os
from EmailSender import *

output_path = "P:\\public\\data\\jy_data\\"
date = ""

def saveJYdata(sql,fname):
	cn = DB_MySQL.DB_MySQL("192.168.0.111",3306,"root","123","jydb","gb2312")
	cn.execute_qry(sql)
	df = pd.DataFrame(cn.table)
	outname = output_path + date+ fname
	df.to_csv(outname,index=False)

def getData2():
	sql = "select * from CT_SystemConst where LB in (1335,1336)"
	saveJYdata(sql,"CT_SystemConst")
	sql = "select * from C_RR_GoalPriceRate"
	saveJYdata(sql,"C_RR_GoalPriceRate")
	sql = "select * from C_RR_ProfitForecast"
	saveJYdata(sql,"C_RR_ProfitForecast")

def getData():
	sql = "select InfoPublDate,AccountingStandards,Mark,secucode,EndDate,EPS,NetProfit,TotalCurrentLiability," \
	      "TotalLiability,CashEquialents,IntangibleAssets,TotalNonCurrentLiability,TotalAssets,NPFromParentCompanyOwners,TotalProfit,SEWithoutMI" \
	      " from LC_MainDataNew A,secumain B where A.CompanyCode=B.CompanyCode and mark='2' and secucategory=1" \
	      " order by EndDate,InfoPublDate desc"
	saveJYdata(sql,"LC_MainDataNewAll.csv")
	print("LC_MainDataNewAll finish")
	sql = "select AccountingStandards,EndDate,LongtermLoan,BondsPayable,LongtermAccountPayable,SpecificAccountPayable," \
	      "EstimateLiability,DeferredTaxLiability,OtherNonCurrentLiability,NCLExceptionalItems,NCLAdjustmentItems,secucode,InfoPublDate " \
	      "from LC_BalanceSheetAll A,secumain B where A.CompanyCode=B.CompanyCode and IfAdjusted=2 and IfMerged = 1 and secucategory=1 " \
	      "order by EndDate,InfoPublDate desc"
	saveJYdata(sql,"LC_BalanceSheetAllTotal.csv")
	print("LC_BalanceSheetAllTotal finish")
	sql = "select secucode,FirstIndustryCode, FirstIndustryName ,SecondIndustryCode,SecondIndustryName," \
	      "ThirdIndustryCode,ThirdIndustryName,InfoPublDate,Standard from LC_ExgIndustry A,secumain B where " \
	      "A.companycode=B.companycode " \
	      "and SecuCategory=1 and (Standard=9 or  Standard=24) order by InfoPublDate"
	saveJYdata(sql,"LC_ExgIndustry.csv")
	print("LC_ExgIndustry finish")
	sql = "select secucode,outdate,indate from LC_IndexComponent A, secumain B where A.secuinnercode=B.innercode and " \
	      " IndexInnerCode=4982 "
	#saveJYdata(sql,"LC_IndexComponent.csv")
	print("LC_IndexComponent finish")
	sql = "select B.innercode,B.secucode,InfoPublDate,EndDate,Mark,PeriodMark,TotalProfit," \
	      "NPParentCompanyOwners,TotalAssets,SEWithoutMI  from LC_PerformanceLetters A, secuMain B " \
	      "where A.companycode=B.companycode " \
	      "and B.SecuCategory=1 and PeriodMark=3 and Mark=2 " \
	      "order by B.innercode"
	saveJYdata(sql,"Letters.csv")
	print("Letters finish")
	sql = "select B.SecuCode,B.innercode,A.InfoPublDate,A.EndDate,A.EGrowthRateFloor, A.EGrowthRateCeiling," \
	      "EProfitFloor,EProfitCeiling, ForecastObject " \
	      "from LC_PerformanceForecast A,SecuMain B  where A.CompanyCode=B.CompanyCode and SecuCategory=1 " \
	      "and ForecastObject=10"
	saveJYdata(sql,"preearning.csv")
	print("LLC_indexcomponentsweight success")
	sql = "select IndexCode,SecuCode,Weight,EndDate from LC_IndexComponentsWeight A, secumain B  where IndexCode in ('3145','4978')"  \
                "and A.innercode=B.innercode and SecuCategory=1  order by EndDate"
	#saveJYdata(sql,"LC_indexcomponentsweight.csv")
	print("LC_MainDataNewAll finish")

if __name__ == '__main__':
	getData()
	date = time.strftime("%Y%m%d",time.localtime())
	date += "."
	opath =  "P:\\yliu\\jy_data\\"+date[0:8]
	if not os.path.exists(opath):
		os.makedirs(opath)
	output_path = opath + "\\"
	#shutil.copytree(output_path,opath)
	#getData()
	em = EmailController()
	em.mail_user = "pubserver@xtcapital.net"
	em.content = "JYDB update successful"
	em.mail_pass = "PSxtcapital@123"
	em.to_list.append("yliu@xtcapital.net")
	em.to_list.append("465274312@qq.com")
	em.subject = "[Success] " + date + " JYDB data"
	em.SendMail()
